8 — Reporting Mechanisms

by Conrad Scott

Crystal Reports for Visual Basic

Quick Steps for Creating a Report
Setting Options

General
Database
Format
Fonts

Examining the Toolbar
Report Creation

Databases and Data Files
Adding and Removing a Database from a Report
The Report Gallery Dialog
Inserting Fields
Record Selection Criteria
Sort Order
Object Linking and Embedding

The Crystal Reports OCX

Adding the OCX Control to a Visual Basic Project
Control Properties
Crystal Reports OCX Examples

Creating Reports Using VBPRINT.DLL

Using VBPRINT
A Sample Printing Program Using VBPRINT.DLL
VBPRINT.DLL Functions

Summary

8 — Reporting Mechanisms

by Conrad Scott

Visual Basic 4.0 is a powerful tool for the development of Windows applications. Many of the applications developed in Visual Basic require some sort of reporting mechanism in order to print the data associated with them. This chapter outlines reporting tools available and discusses methods of incorporating reporting into your Visual Basic applications.

Crystal Reports for Visual Basic

One tool for report development is Crystal Reports for Visual Basic. Crystal Reports is supplied with each copy of Visual Basic 4.0 (both 16 and 32 bit). This is an upgraded version of the application distributed with Visual Basic 3.

Crystal Reports is comprised of two major components. The first is the report development interface (\VB\REPORTS\CRW.EXE) (see Figure 8.1). The application icon is labeled "Crystal Reports - 16 bit" or "Crystal reports - 32 bit". This is the interface in which the report layout is designed. Designing a report includes selecting a source database, adding data fields to the report, setting record criteria, sorting, and so on. The second component is the Crystal Reports custom control (\WINDOWS\SYSTEM\CRYSTL16.OCX - 16 bit or CRYSTL32.OCX - 32 bit). This custom control is added to the Visual Basic application and handles the generation and printing of previously created reports. If you did not choose to install Crystal Reports when you initially installed Visual Basic, run Visual Basic setup again and choose Custom Installation. Choose only Crystal Reports from the list of options for installation to install Crystal Reports. This installation adds both the development interface and the OCXs to your system.

Figure 8.1. The Crystal Reports development interface main application window.

The following section outlines Crystal Reports in detail. The section begins with the steps that experienced developers can use to quickly generate a report. Following the quick steps is a detailed tour of the application, along with techniques for interfacing Crystal Reports with Visual Basic applications.

Quick Steps for Creating a Report

If you are already familiar with other reporting packages such as Microsoft Access or an earlier version of Crystal Reports, you will find creating a report in Crystal Reports intuitive. After running the Crystal Reports interface, do the following:

Select File | New | Report from the main menu, or click the new report icon on the toolbar (first toolbar icon). The New Report dialog is displayed.
Choose the type of report you would like to create from the Create new: section and the source of the report data from the from: section.
Choose OK. The Choose Database File dialog appears. Select a database to report against in the dialog box or choose SQL Table... to log on to a server via ODBC. If you choose to log on to a server you are returned to the Choose Database File dialog at completion of logon to select a database to report against.
The Choose SQL Table dialog appears. Select a table from the database to report against and choose OK. The new report is created and the Insert Database Field dialog appears.
Select the fields that you would like to appear on the report (extended- and multi-selection are allowed) and drag them to the report window's Details section.
Choose Insert | Text Field... from the main menu. The Edit Text Field dialog appears. Type in a title for the report and choose Accept. Move the mouse to the Page header section and click the left mouse to place the field on the report.
Choose File | Print Preview to see a preview of the report.

That's it. You have just created a report. Obviously, these are the most basic steps for report generation. Notice that although these steps do produce a report, it is not the most glamorous or eye-catching one imaginable. However, this does give you an idea of how simple creating a report can be.

The following sections examine the features of Crystal Reports in more detail. These sections include setting options for Crystal Reports, adding sections to a report, record selection, and formula creation, exporting data, OLE automation and more.

Setting Options

Select File | Options from the main menu to display the Crystal Report Options dialog (see Figure 8.2). This dialog contains a Category section with four buttons:

Figure 8.2. The Options dialog displaying the General category.

General

This category contains general settings for Crystal Reports (see Figure 8.2):

Use Report Gallery for new reports—Choose whether to use the Report Gallery (report creation wizard) for the creation of a new report. If Report Gallery is selected, use the Prefer to Create New Reports From: section to set the default data location (Data Files, SQL table, Crystal Dictionaries, Other Reports).

The General category also contains check boxes that set whether to display the button bar, format bar, and status bar.

Use Short Section Names—Select the Use Short Section Names check box to shorten the names of the sections in the report design window. This provides more space for the report layout. When this option is selected, the margin that displays the section names is truncated so that more of the report is visible.
Show Field Names—When this check box is checked, Crystal Reports displays the field name in the center of each field on the report. Left unchecked, the field format is displayed.
Insert Detail Field Titles—When a new field is added to the report and this option is checked, Crystal Reports adds a field title just above the inserted field. This field title is displayed in both design and run modes.
Refresh Data on every print—When this option is on, Crystal Reports refreshes the report data from the selected database each time the report is printed. The report is also refreshed when it is exported to a data file or via MAPI (Mail API). Assume that this option is checked. Imagine that you are using a database that is updated frequently and you send a report via MAPI to several people in your division. If the database is updated in between mailnotes, each recipient will get a different report. This also applies if the report is sent to the printer. If multiple reports should contain the same data, uncheck this option.
Save data with closed report—When checked, Crystal Reports saves the actual report data in a compressed format when the report is closed. When re-opened, the report data is decompressed and used to display in the report. This option can be used when the database is excessively large and only sample data is needed for report development. Another use for this is a report that is generated monthly. Once the report is generated, checking this option produces the saved month-end reporting each time the report is viewed.
Report Directory—Sets the directory that Crystal Reports searches for existing reports. Choose Browse... to display a common dialog box in order to change the default directory.
Mail Destination—Use this combo box to select to which mail destination to export reports. Generally, Mail via MAPI is the only selection available in this combo box.

Database

This category contains database settings for Crystal Reports.

Figure 8.3. The Options dialog displaying the Database category.

Data Directory—The data directory sets the default directory Crystal Reports uses to list databases in the Choose Database File dialog. This common dialog appears with the current data directory set as the default (the default upon installation of Crystal Reports is \VB4\REPORT).
Data Selector—The data selector edit box lists the database extensions to display in the Choose Database File dialog. The extensions listed in this edit box appear in the List Files Of Type combo box in the Choose Database File dialog.
Index Selector—The index selector sets the extensions relating to indexing (Index file dialog, and so on) that appear in dialog boxes. Indexes are set by selecting Database | Links from the main menu (the Links dialog box appears) and pressing New to display the Define Link dialog. This dialog is used to link database tables using key fields. The purpose of indexing is to display reporting data from several tables at the same time.
Use Indexes For Speed—This check box sets whether indexes defined in the Define Link dialog are used in the report generation. Using indexes speeds up the generation of a report.
Use Default Alias—This check box indicates whether the database's 8-character filename is used as the default database name in Crystal Reports.
Translate DOS Strings—This check box sets whether the character set used in dBASE applications (ASCII) is translated to the windows character set (ANSI). If a dBASE database's string fields contain any of the ASCII characters from 128—255 and are used to generate a report in Crystal Reports, this option should be checked so that the characters are properly translated to ANSI.
Translate DOS Memos—This is the same as Translate DOS Strings except that it applies to memo fields.
Default Dictionary—Allow for the use of a previously created data dictionary for the report. A data dictionary contains only pertinent report information. This is created from the reporting database prior to entering Crystal Reports. A data dictionary is essentially a data file containing the result of a query that links several tables in the reporting database and extracting only information necessary for the report.
SQL—Choose the SQL Options... button to bring up the SQL Options dialog. This dialog is used to set default settings for SQL operations. If no options are set in this dialog box, multiple dialogs are presented during the SQL table selection process. Items covered in this dialog include the server type, server name, database, and User ID. SQL statements for tables and owners can be set in this dialog, and warnings can be set if the query yields more than a certain number of records.

Format

This category is used to set field format information for reports. There are five command buttons in this dialog (see Figure 8.4):

Figure 8.4. The Options dialog displaying the Format category.

String...—Choose this button to display the Format String dialog. This dialog contains the following controls:

Suppress if duplicated check box—When checked, duplicate data is not printed from record to record. For example, imagine that you are creating a report that displays information about a customer order. The order contains an order number and several items. You want to display the order number only one time for each order, regardless of the number of items on the order. When the Suppress if duplicated check box is checked, the order number is listed only on the first item in an order. If the check box is unchecked, the order number appears on each item in the order.

Hide when printing—When checked, the printing of string fields is suppressed. These fields appear in the report design window for reference.

Alignment—Sets the alignment for text in the field (default, left, centered, right).

Print on multiple lines—Check this check box if you would like to allow a field to print on multiple lines.

Maximum number of lines:—If the Print on multiple lines check box is selected, enter the maximum number of lines that the field is allowed to print on (enter 0 for no limit).
Number...—Choose this button to display the Format Number dialog. This dialog contains the following controls:

Use Windows Default Format—Uses the Windows default date format for date fields on the report.

Suppress if Duplicated—When checked, duplicate data is not printed from record to record. For example, the order date is listed only on the first item in an order. If the check box is unchecked, the order date appears on each item in the order.

Suppress if Zero—Prevents the printing of number fields with a 0 value.

Hide When Printing—When checked, the printing of number fields is suppressed. For reference, these fields appear in the report design window.

Alignment—Sets the alignment for a number in a field (default, left, centered, right).

Decimals—Sets the number of decimal places displayed in a number field (1.0, 1.00, 1.000, and so on).

Rounding—Sets the number of places rounded to in a numeric field.

Negatives—Sets the format for the display of negative numbers in number fields (-123, 123- (123)).

Currency Symbol—Sets the currency symbol for the number fields ($, £, and so on).

One Symbol Per Page—Enabled when the Currency Symbol check box is checked. Prints the currency symbol for the first record but no others.

Fixed/Floating/Position—Sets the alignment for number fields when Currency Symbol is selected. The choices are to align the field to the dollar sign ($14,000—$400.00) or align the field to the value ($14,000—$—400.00).

Decimal Separator/Thousands Separator—Sets the characters used for the decimal and thousands separator.

Leading Zero—When checked, a zero is added to integer values less than 1 (0.2356).
Currency...—Choose this button to display the Format Currency dialog. This dialog is exactly the same as the Number... dialog.
Date...—Choose this button to display the Format Date dialog. Fields unique to this dialog are:

MDY/DMY/YMD—Sets the date display order for date fields.

Month/Day/Year—Sets each of these sections' display format for date fields.
Boolean...—Choose this button to display the Format Boolean dialog. This dialog is exactly the same as the String... dialog with the following exception:

Boolean Text—Sets the display format of True and False values on a report. The options are:

True or False
T or F
Yes or No
Y or N
1 or 0

Fonts

This section contains 12 command buttons, each representing different sections of the report (see Figure 8.5). Choosing any one of these buttons displays a common font dialog that allows font settings for the selected section.

Figure 8.5. The Options dialog displaying the Fonts category.

Examining the Toolbar

The Crystal Reports toolbar provides quick access to many of the most common tasks performed while creating a report. The following explains how each toolbar icon is used:

Create a new report—The New Report dialog box is displayed asking for the type of report to be created and the source of the report data. (This assumes that Use Report Gallery for new reports in the General section of the options dialog is checked.)

Open an existing report—A common dialog box is displayed asking for the filename of the report. Once selected, the report is opened in design mode within Crystal Reports.

Save report—Save the report that currently has the focus. If the report has not been previously saved, the File Save As dialog appears asking for a report filename.

Preview report—Previews the report in a viewer window. This option brings up the Saved Data dialog box. Choose Use Saved Data to use the current report data or Refresh Data to refresh the report using data from the selected database.

Print report to a printer—Prints the report to the default Windows printer. This option brings up the Saved Data dialog box. Choose Use Saved Data to use the current report data or Refresh Data to refresh the report using data from the selected database. The Print dialog appears when printing options can be selected.

Export a report—Export report data in one of 15 formats to either a disk file or via MAPI. Selecting this option brings up the Export dialog box where the export format and destination are selected. Once selected, the Character-Separated Values dialog, Number and Date Format Dialog, and Choose Export File dialog appear.

Mail a report—Follows the same path as the Export a report option with the exception of the Choose Export File dialog box. At this point a Send Mail window appears (similar to Microsoft Mail). Once populated, the report data is attached to a mailnote sent to the selected recipient(s).

Cut—Cut the selected item from the report and place it on the Windows Clipboard.

Copy—Copy the selected item from the report and place it on the Windows Clipboard.

Paste—Paste the contents of the Windows Clipboard at the current cursor position on the report.

Select Fields—Allows for using the mouse to select multiple fields on the report. Click this icon, hold the left mouse button down and drag to select multiple controls. During selection a rectangle is drawn around the controls. Release the mouse button to complete selection.

Insert Database Field—Brings up the Insert Database Field dialog box that allows for the addition of database fields to the report. Drag fields from this dialog to add them to the report or choose the Insert button. Choose Browse Field Data... to view a sample of the data from the highlighted field. Choose Done when field selections are complete.

Insert Text Field—Brings up the Edit Text Field dialog box. Type a name for the new text field and choose Accept. A rectangular field appears on the report. Move the mouse to position the field and click the left button to place the field in the desired location.

Insert Formula Field—Brings up the Insert Formula dialog. Enter a name for the new formula in the edit box and press OK. The Edit Formula: @Formula Name dialog appears allowing for the selection of formula criteria. See the section, "The Formula Editor," later in this chapter for more information about creating formulas.

Insert Summary Field—Brings up the Insert Summary dialog prompting for information about the new summary field. See the "Inserting Fields" section later in this chapter for more information about summary fields.

Insert Graphic—Brings up a common dialog box asking for the name of the graphic file to insert in the report. When selected, a rectangle appears in the report design window. Move the mouse to position the graphic in the desired position and click the left mouse to place the graphic on the report. The graphic appears at both design time and run time.

Insert OLE Object—Brings up the Insert Object dialog box. Select the type of OLE object to be inserted into the report and choose OK. When selected, a rectangle appears in the report design window. Move the mouse to position the graphic in the desired position and click the left mouse to place the graphic on the report.

Draw a line—Changes the cursor to a pencil and allows a line to be drawn on the report.

Draw a box—Changes the cursor to a pencil and allows a box to be drawn on the report. The box is a graphical object used for improving the look of a report. The box can act as a frame for fields (fields can be dragged inside the box area). Select the box and click the right mouse button to display a pop up menu of options for the box. Select Change Format... from the pop-up menu to display a formatting dialog.

Record Sort Order—Brings up the Record Sort Order dialog allowing the report's primary and secondary sort order to be set.

Record Selection Criteria—Brings up the Edit Formula: Record Selection Formula dialog. Use this dialog to set the criteria for what records appear on the report.

Bold—Standard toolbar icon for bolding text.

Italic—Standard toolbar icon for italicizing text.

Underline—Standard toolbar icon for underlining text.

Increase Font Size—Increases the font size of text in the selected field.

Decrease Font Size—Decreases the font size of text in the selected field.

Left Align—Left aligns the data in the selected field.

Center—Centers the data in the selected field.

Right Align—Right aligns the data in the selected field.

Currency—Enabled when a currency field is selected. Inserts a dollar sign to the left of the value ($1,000,000).

Comma—Enabled when a currency field is selected. Inserts a comma in the dollar value (1,000,000).

Percent—Enabled when a currency field is selected. Inserts a percent sign to the right of the value (1,000,000%).

Degree of Precision (Increase)—Inserts a decimal place in a currency field. The number 100.55666 is rounded to 100.56. Changing the degree of precision changes 100.56 to 100.556, 100.5666, and so on.)

Degree of Precision (Decrease)—Removes a decimal place in a currency field. The number 100.55666 is rounded to 100.56. Changing the degree of precision changes 100.556 to 100.56, and so on.)

Report Creation

This section outlines the report creation features of Crystal Reports. Items covered include accessing databases and files, The Report Gallery (Report Wizard), inserting fields, creating formulas, and inserting graphical objects.

Databases and Data Files

Crystal Reports is capable of accessing reporting data from a variety of database formats. These formats include dBASE, InterBase, Btreive, Paradox, as well as all Microsoft formats (Access, Excel, FoxPro, and so on).

If you are using a database via ODBC, you first need to log on to the ODBC server. To log on to a server, Select Database | Log On Server from the main menu. Select an ODBC server from the list and choose OK. You may log on to several ODBC servers concurrently, but you must log onto each individually. Once you are logged on to a server, you have access to all databases contained within that server.

Adding and Removing a Database from a Report

Reporting data is accessed from many different types of databases by Crystal Reports. To add a database to a report, select the Database | Add Database to Report... menu option. This brings up the Choose SQL dialog. If you are adding an additional table from the current database to the report, choose the table from the SQL Tables listbox and choose OK. To add a new database to the report, choose Database File... This option brings up a common dialog box from which you may select an additional database to add to the report. If this database is from an ODBC server that you have not logged on to, you are prompted to log on to the server.

The Report Gallery Dialog

To create a new report, select File | New from the main menu. A menu with three options appears:

Report
Cross-Tab
Mailing Label

Selecting any one of these options brings up the New Report dialog box. The buttons that are selected by default on this dialog depend on the menu option you selected. If you do not see the New Report dialog, select File | Options from the main menu (the Options dialog appears). In the general category, select the Use Report Gallery for new reports check box.

In the New Report dialog, select the type of report that you would like to create from the Create new: section. This section has the following buttons:

Report—Creates a detail report. A detail report has this format:

*Customer Order Detail for September, 1995*
*Order Number*	*Customer Number*	*Serial Number*
123456789	0000010123	321654987
321564987	5605640560	987906500
654056065	0987089980	654970900

Cross Tab—A crosstab report summarizes data much like a spreadsheet that compares the data in fields of one category with the data in fields of another category. A crosstab report has this format:

*Number of Products by Month*
	*January*	*February*	*March*
Product 1	1200	942	1040
Product 2	640	670	920
Product 3	80	1020	1030

Label—This options allows for the creation of mailing labels.

Inserting Fields

Inserting a field into a report is begun by selecting one of the Insert menu options. The following is an explanation of the type of fields that can be inserted into a report along with how to add each type to your report layout. Once a field is inserted into the report, right clicking on the field pops up a menu with formatting options for the field. You can also move the field from where Crystal Reports places it, to anywhere else on the report.

Database Fields

This option inserts a field from the database that you have chosen for your report. Select Insert | Database Field... from the main menu to display the Insert Database Field dialog box. Select the field(s) that you would like to add, and drag them to your design window or choose the insert button (multi-selection and extended selection are allowed). To view a sample of the data for the selected field, choose the Browse Field Data... button. This option displays a dialog box containing a sample of the data contained within the field. Choose Done when you finish adding browsing field data and when you finish adding fields choose Done from the Insert Database Field dialog.

Text Fields

To insert a text field into your report, Select Insert | Text Field... from the main menu. This option displays the Edit Text field dialog. Enter a name for the new field and choose Accept to add the field to the report.

Formula Fields

This option allows you to insert calculated fields into your report. An example of when this is useful is a field that calculates the total number of orders placed in a given month, the sum of all dollars received, and so on. Select Insert | Formula Field.. from the main menu to display the Insert formula dialog box. Enter a name for your new formula field and choose OK to bring up the Edit formula: @ Formula name dialog box. For this example, assume you are using an order entry database. The following are four sections to this dialog. Functions and fields are presented in the order you would use them.

Functions: Assume that you are interested in calculating the number of orders in your database. The first step is to select the Count(fld) function from the Arithmetic: section.

Fields: To select a field, double-click on it from the list of available database fields. This field is used to calculate the new formula. For our example, you select the Order Number field (ORDERNUM). Your calculation now reads: Count ({ORDERS.ORDERNUM}). When added to the report, this field displays the number of orders contained in the data from your database.

Operators: You need to know the current inventory amount (CURRINV) minus the number of orders received. Double-click CURRINV in the Fields list box. Next double-click Subtract in the Arithmetic section of the Operators list box. Now select the Count(fld) function from the Arithmetic: section and then the ORDNUM field from the Fields: section. Our finished calculation reads: {ORDERS.CURRINV} - Count ({ORDERS.ORDERNUM}). This field displays the available inventory after all existing orders are fulfilled.

Formula Text: As you create the formula, it is displayed in the Formula Text: section. You can edit this section manually if you wish.

When you are satisfied with your field formula, choose Accept or Check to have the Crystal compiler check the formula. If there are errors in the formula a message box appears describing the problem. After choosing Accept, the dialog closes and a field appears on the report. Move the mouse to position the field and click the left button to place the field in the desired location.

Special Fields

This option allows you to insert page numbers, record numbers, group numbers, and the date the report is printed. Selecting one of these options places the selected field on the report layout.

Record Selection Criteria

Choosing the records that you would like to appear on your report is done through the same dialog used to create formula fields. See the "Formula Fields" section earlier in this chapter for more details. Use this dialog to screen the records that you would like to appear on the report.

Sort Order

Setting a sort order for the report is done by choosing Report | Record Sort Order... This displays the Record Sort Order dialog box. The Report Fields: section of the dialog displays a list of fields that can be sorted. Fields that are currently being used for sorting are displayed in the Sort Fields: section. Select a field from the Report Fields: section and choose Add (or double-click) the field to add it for sorting. Multi-level sorting is accomplished by choosing multiple fields. For example, assume that you are using an order entry database and would like to see a detail of the orders you currently have in your database. You want this information sorted by region (REGION) and then by product (PRODUCT). Choosing REGION and then PRODUCT in the Record Sort Order dialog sorts the report in this manner:

*Orders for the Month of September, 1995*
*Order Number*	*Customer Number*	*Product Name*
Region 1
123456789	654321987	Product AAA
564065400	320646060	Product BBB
Region 2
65406406	5640654056	Product BBBB
5505804	6540662	Product ZZZ

Remember that you need to set up the proper sections in order to display the data in this format.

Object Linking and Embedding

Inserting an OLE object into a report is a simple task. First, select the OLE icon from the toolbar. Select from the list the type of object that you would like to insert and then select OK. There are 25 different objects that you can insert into a report, so the next step depends on which object you select. Generally, you are prompted to select the location of the object's data (a bitmap, media file, and so on). Once selected, the object is embedded into the report. Right click on the inserted object to manipulate. An example of manipulation for the Pocket CD Playlist object (CD player) is playing, pausing, and stopping the CD. This manipulation is also allowed when the report is displayed in the print preview window.

The Crystal Reports OCX

The second component of Crystal Reports is the Crystal Reports OCX (\WIN- DOWS\SYSTEM\CRYSTL16.OCX and \WINDOWS\SYSTEM\CRYSTL32.OCX). This OCX is incorporated directly in to the Visual Basic application and used to print existing Crystal Reports. This section outlines the custom control (the same for both 16 and 32 bit), and provides a sample program using the control.

Adding the OCX Control to a Visual Basic Project

To add the OCX control to a Visual Basic project, open the project and select Tools | Custom Controls... from the Visual Basic main menu. Check the Crystal Report Control item in the list box and choose OK. The Crystal Report Control is added to the Toolbox for the project. Open a form and double-click the control in the Toolbox to add it to the form. Select the control and press F4 to view its properties.

Control Properties

The Crystal Reports custom control contains the following unique properties:

Action

Description: Set this property to 1 to print a report. This property is available at runtime only.

Usage: Form1.CrystalReport1.Action = 1

Connect

Description: Set this property when you want to log on to a SQL server. The property has the following parameters:

DSN—Server Name
UID—User ID to log on to the SQL server
PWD—Password to log on to the SQL server
DSQ—DatabaseQualifier$

The DatabaseQualifier parameter is used if your server uses the database concept and is the name of the database.

Usage: Form1.CrystalReport1.Connect = "DSN = Server1;UID = Conrad;PWD = Vacation;DSQ = Develop"

Note

To use this property you must have first installed an ODBC server and have included the database in your path statement (this path should include \database\bin).

CopiesToPrinter

Description: Sets the number of report copies that are sent to the printer.

Usage: Form1.CrystalReport1.CopiesToPrinter = 5 (sends five copies to the printer).

DataFiles

Description: Sets the database file used to generate the report. You only need to use this property if you would like to print the report from several data locations. The report has a default data location; this array property changes it.

Usage: Form1.CrystalReport1.DataFiles(Index) = LocationOfDataFile$

Destination

Description: Specifies the destination for the report. This property has the following settings:

0 - Window—Pops up a window displaying the report.

1 - Printer—Sends the report to the printer.

2 - File—Prints the report to a file. When using this property you must set the PrintFileName and PrintFileType properties.

Usage: Form1.CrystalReport1.Destination = 0

Formula

Description: This property allows you to change an existing formula in a report. An example of this is a formula that calculates the sales tax for a state. Use this property to change the formula for a different state (this is an array property).

Usage: Report1.Formula(State(Index)) = "Tax = {Orders.Total} * .08" This sets the sales tax formula to 8%.

LastErrorNumber

Description: This is the number of the last error encountered on the report.

Usage: If Form1.CrystalReport1.LastErrorNumber <> 0 Then GoTo ReportErrorHandler

LastErrorString

Description: This is a description of the value in LastErrorNumber.

Usage:

If Form1.CrystalReport1.LastErrorNumber <> 0 Then

    ErrorNumber = Form1.CrystalReport1.LastErrorNumber

    ErrorText = Form1.CrystalReport1.LastErrorString

    MsgBox "Error Number " & ErrorNumber &  ": " & ErrorText & " encountered."

    GoTo ReportErrorHandler

Endif

Password

Description: Set this property to enter a password for an Access table.

Usage: Form1.CrystalReport1.Password = "MyPassword"

PrintFileName

Description: Sets the name of the file where the report is printed (assumes that the Destination property is set to 2 - file).

Usage: Form1.CrystalReport1.Destination = 2

Form1.CrystalReport1.PrintFileName = "c:\vb\reports\MyReport.rpt"

PrintFileType

Description: Use this property to set the format for printing a report to a file.

Usage: Form1.CrystalReport1.PrintFileType = 4 (See the Help topic PrintFileType property in Crystal Reports for an explanation of file formats).

ReportFileName

Description: This property sets the name of the report to be printed.

Usage: Form1.CrystalReport1.ReportFileName = "c:\vb\reports\myreport.txt"

SelectionFormula

Description: Sets the records used when printing a report.

Usage: Selection$ = "{Orders.Total} > 1000"

Form1.CrystalReport1.SelectionFormulaProperty = Selection$

SortFields

Description: Sets the sort order for the report. This is an array property. The usage example initiates a primary and secondary sort:

Usage: Form1.CrystalReport1.SortFields(0) = "+{ORDERS.ORDNUM}" 'primary

Form1.CrystalReport1.SortFields(1) = "+{ORDERS.ORDDATE}"    'secondary

This sorts the report by order number and then by order date.

UserName

Description: Sets the user name when logging on to a protected Access database.

Usage: Form1.CrystalReport1.UserName = "MyUserName"

WindowBorderStyle

Description: Sets the border style for the report window. The settings are:

0—None

1—Fixed Single

2—Sizable

3—Fixed Double

Usage: Form1.CrystalReport1.WindowBorderStyle = 1

WindowControlBox

Description: Sets whether or not a control menu appears in the upper left hand corner of the report window. This assumes that the Destination property is set to 0.

Usage: Form1.CrystalReport1.WindowControlBox = TRUE

WindowHeight

Description: Sets the height of the report window in pixels.

Usage: Form1.CrystalReport1.Height = 300

WindowLeft

Description: Sets the number of pixels from the left edge of the parent window in which the report window appears. If the report window is the parent window, this is relative to the desktop window.

Usage: Form1.CrystalReport1.WindowLeft = 300

WindowMaxButton

Description: Sets whether a maximize menu appears in the upper right hand corner of the report window. This assumes that the Destination property is set to 0.

Usage: Form1.CrystalReport1.WindowMaxButton = FALSE

WindowMinButton

Description: Sets whether a minimize button appears in the upper right hand corner of the report window. This assumes that the Destination property is set to 0.

Usage: Form1.CrystalReport1.MinButton = FALSE

WindowParentHandle

Description: Sets the handle of the parent window if the report window is a child of another window. The Usage section assumes that the report window is a child of Form1.

Usage: Form1.CrystalReport1.ParentHandle = Form1.hWnd

WindowTitle

Description: Sets the title for the report window. This assumes that the Destination property is set to 0.

Usage: Form1.CrystalReport1.WindowTitle = "My New Report"

WindowTop

Description: Sets the number of pixels from the top edge of the parent window that the report window appears. If the report window is the parent window, this is relative to the desktop window.

Usage: Form1.CrystalReport1.WindowTop = 100

WindowWidth

Description: Sets the width of the report window in pixels. This assumes that the Destination property is set to 0.

Usage: Form1.CrystalReport1.WindowWidth = 300

Crystal Reports OCX Examples

The following examples utilize the reporting features of the Crystal Reports OCX from within Visual Basic.

First we send a report to the printer.

Private Sub Print_Report Click()

    Form1.CrystalReport1.Destination = 2

    Form1.CrystalReport1.ReportFileName = "c:\vb\reports\MyReport.txt"

    Form1.CrystalReport1.Action = 1

End Sub

Next we send the report to the printer, then to a data file, and then open it in the report window.

Private Sub Print_Report Click()

    'to the printer

    Form1.CrystalReport1.Destination = 1

    Form1.CrystalReport1.ReportFileName = "c:\vb\reports\MyReport.txt"

    Form1.CrystalReport1.Action = 1

    'to a data file

    Form1.CrystalReport1.Destination = 2

    Form1.CrystalReport1.PrintFileName = "c:\vb\reports\output.txt"

    Form1.CrystalReport1.PrintFileType = 0   'Record format

    Form1.CrystalReport1.Action = 1

    'and finally the report window

    Form1.CrystalReport1.Destination = 0

    Form1.CrystalReport1.Action = 1

End Sub

Next we send the report to a window containing no minimize button, maximize button, or control menu. We set the window to 100 pixels from the top and left of the parent window (if there is no parent window, it is aligned based on the desktop). We also add a title.

Private Sub Print_Report Click()

    Form1.CrystalReport1.Destination = 0

    Form1.CrystalReport1.ReportFileName = "c:\vb\reports\MyReport.txt"

    Form1.CrystalReport1.WindowMinButton = FALSE

    Form1.CrystalReport1.WindowMaxButton = FALSE

    Form1.CrystalReport1.WindowControlBox = FALSE

    Form1.CrystalReport1.WindowTop = 100

    Form1.CrystalReport1.WindowLeft = 100

    Form1.CrystalReport1.WindowTitle = "My New Report"

    Form1.CrystalReport1.Action = 1

End Sub

Finally, create and print a report for each of six divisions. We sort the report by Order Number and Order Date and send it to the printer.

Private Sub Print_Report Click()

    'counter for loop

    Dim iRet as Integer

    'Set the number of divisions

    Dim NumDivisions as Integer

    NumDivisions = 6

    'record restriction string for each division

    Dim DivisionFormula as String

    'set the report destination and report name

    Form1.CrystalReport1.Destination = 1

    Form1.CrystalReport1.ReportFileName = "c:\vb\reports\MyReport.txt"

    'add a primary and secondary sort

    Form1.CrystalReport1.SortFields(0) = "+{ORDERS.ORDNUM}"    'primary sort

    Form1.CrystalReport1.SortFields(1) = "+{ORDERS.ORDDATE}"    'secondary sort

    For iRet = 1 to NumDivisions

        'set the division and apply it to the report

        DivisionFormula = "{ORDERS.DIVISION} = " &  Str$(iRet%)

        Form1.CrystalReport1.SelectionFormula    Property = DivisionFormula

        'print the report

        Form1.CrystalReport1.Action = 1

    Next iRet

End Sub

Creating Reports Using VBPRINT.DLL

VBPRINT.DLL is a dynamic link library (DLL) available through the Microsoft Developer's Network (MSDN). This DLL provides a variety of formatting and drawing functions for printing from a Visual Basic application. It may be distributed royalty free with your Visual Basic applications. This DLL was ported to 32 bit (VBPRNT32.DLL) and by Paul Mahar at MicroHelp Inc. in Atlanta (Thanks, Paul!). The 16- and 32-bit DLLs, as well as the source code for each are included with this book.

VBPRINT.DLL is ideal for creating reports directly from Visual Basic using data from either a database or data file. The DLL contains functions to build a report layout. Functions included add headers and footers to your report as well as allow you to set up columns for the display of data. Rectangles, round rectangles, and lines can be placed on the report to enhance the graphical look.

Note

VBPRINT.DLL does not output to Visual Basic forms or to data files. This is strictly a printing DLL. If you set the printer destination to Print to File, the file created contains only printer directives.

The following section describes the ways that you can use VBPRINT.DLL to create and print reports directly from your Visual Basic application. Following the explanation is an example that incorporates the features of VBPRINT.DLL. Finally, a dictionary of the DLL's functions is provided.

Using VBPRINT

This section describes the steps necessary to design and print a report using VBPRINT.DLL. This report contains an underlined title, columns of data, and is fully formatted. The report is output to the windows default printer.

Here are the function declaration statements for VBPRINT:

Declare Function InitializePrinter Lib "vbprint.dll" (ByVal hWnd%) As Integer

    Declare Function PrinterSetup Lib "vbprint.dll" (ByVal hWnd%) As Integer

    Declare Function PageLayoutSetup Lib "vbprint.dll" (ByVal nTop%, ByVal nRight%, _ByVal nBottom%, ByVal nLeft%) As Integer

    Declare Function DonePrinting Lib "vbprint.dll" () As Integer

    Declare Function StartParagraph Lib "vbprint.dll" (ByVal szFontName$, ByVal _nFontSize%, ByVal nStyle%) As Integer

    Declare Function FinishParagraph Lib "vbprint.dll" () As Integer

    Declare Function PrintHeadline Lib "vbprint.dll" (ByVal szHeadLine$, ByVal _szFontName$, ByVal nFontSize%, ByVal nStyle%) As Integer

    Declare Function ParagraphText Lib "vbprint.dll" (ByVal szText$) As Integer

    Declare Function EjectPage Lib "vbprint.dll" () As Integer

    Declare Function PrintDLLVersion Lib "vbprint.dll" () As Integer

    Declare Function SetParagraphSpacing Lib "vbprint.dll" (ByVal nSpacingBefore%, _ByVal nSpacingAfter%) As Integer

    Declare Function SetUpColumns Lib "vbprint.dll" (ByVal Columns%, nC As Any, _ByVal szFontName$, ByVal nFontSize%, ByVal nStyle%) As Integer

    Declare Function PrintColumnText Lib "vbprint.dll" (ByVal szText$) As Integer

    Declare Function EndColumnPrinting Lib "vbprint.dll" () As Integer

    Declare Function PrintColumnHeaders Lib "vbprint.dll" (ByVal szHeader$, ByVal _Columns%, nC As Any, ByVal szFontName$, ByVal nFontSize%, ByVal nStyle%) As _Integer

    Declare Function KillJob Lib "vbprint.dll" () As Integer

    Declare Function PrinterPort Lib "vbprint.dll" (ByVal szPort$) As Integer

    Declare Function PrinterName Lib "vbprint.dll" (ByVal s$) As Integer

    Declare Function PageSizeY Lib "vbprint.dll" () As Integer

    Declare Function PageSizeX Lib "vbprint.dll" () As Integer

    Declare Function DrawLine Lib "vbprint.dll" (ByVal X1%, ByVal Y1%, ByVal X2%, _ByVal Y2%) As Integer

    Declare Function DrawRectangle Lib "vbprint.dll" (ByVal X1%, ByVal Y1%, ByVal _X2%, ByVal Y2%) As Integer

    Declare Function DrawRndRectangle Lib "vbprint.dll" (ByVal X1%, ByVal Y1%, _ByVal X2%, ByVal Y2%, ByVal X3%, ByVal y3%) As Integer

    Declare Function DrawEllipse Lib "vbprint.dll" (ByVal X1%, ByVal Y1%, ByVal _X2%, ByVal Y2%) As Integer

    Declare Function MoveYPos Lib "vbprint.dll" (ByVal nY%) As Integer

The first step is to create a structure to hold the data that is to be displayed on the report. The data contained in the structure is fed to the VBPRINT functions to output the data to the printer. For purposes of example we create the following structure:

Type OrderData

        OrderNum As String

        OrderDate As String

        CustNum As String

        SerNum As String

End Type

Fill this structure with data from your database (using SQL statements, file reads, and so on). The next step is to call the InitializePrinter() function. Pass this function the hWnd of the application form (the handle to a form named form1 is form1.hWnd). The InitializePrinter function prepares the printer to receive data from the application.

i% = InitializePrinter(Form1.hWnd)

Note

An hWnd is a handle to global information about an object (form, control, and so on). In this case, the hWnd denotes an instance of Form1. This handle is used by the DLL to get information from the form so that the printer can be initialized.

Next, use the PageLayoutSetup() function to set the margins for the page. This function sets the area in which printing is allowed. A hint here is that after you send a command to add data to the page, you can change these settings without erasing previously printed items.

'Set the margins for the page layout (top, right, bottom, left)

i% = PageLayoutSetup(10, 100, 100, 100)

The StartParagraph() function is then used to set the font that is used for printing, the size of the font and any number of style flags. See the StartParagraph() function under the function descriptions for a listing of these flags.

'Begin a paragraph. Set the font to Arial 12 point bold)

i% = StartParagraph("Arial", 10, BOLD_FONT)

Next you print a title for the report using the PrintHeadline() function. szHeadline is the text that is used for the title. Unfortunately, there does not appear to be a way to center the title. Several tabs added to the title (Chr$(9) & Chr$(9)) provide a way around that.

'Print a title for the report (using the font and size set in StartParagraph

 i% = PrintHeadline(szHeadLine$, "Arial", 14, BOLD_FONT)

After the title is printed, underline it using the DrawLine() function. This works the same way as the Visual Basic Line() function.

'Draw a line under the report title (left X, left Y, right X, right Y)

i% = DrawLine(100, 1500, 12000, 1500)

In this example, we have four columns of data to print on the report. This data is contained in the OrderData structure outlined above. The SetUpColumns() function is called to add four columns, each with a width of 100 (1 inch) using the 12 point, bold, Arial font.

'Set up 4 columns in which to print the reporting data.

i% = SetUpColumns(4, 100, "Arial", 12, BOLD_FONT)

We next use the MoveYPos() function to add some space between the line and the column headings. The value passed to the function is in hundredths of an inch.

MoveYPos (40)

Next we draw a line under the column headings. Usually this function is called after the column headings are printed, but I placed it here to bring up an important point. It is likely that after you print your title and underline it, you will change font sizes to print your column headings. In order to avoid having the line under the title appear bolder than the one under the column headings, draw the line before changing fonts.

'Draw a line under the column headings

i% = DrawLine(100, 2100, 12000, 2100)

To change fonts to begin printing the data, call the FinishParagraph() function followed by another StartParagraph() function. The second StartParagraph() function changes the font settings prior to printing the report data.

Next, we place the column titles in the variable szHeader$. The columns are separated by a tab (Chr$(9)):

'Construct and print the titles for the columns

szHeader$ = "Order Number" + Chr$(9) + "Order Date" + Chr$(9) + "Customer Number " _+ Chr$(9) + "Serial Number"

After the columns are set up, we call the PrintColumnHeaders() function:

i% = PrintColumnHeaders(szHeader$, 4, ColumnWidth(1), "Arial", 12, BOLD_FONT)

The next step is to use the MoveYPos() function to add some space between the column headings and the data.

MoveYPos (10)

Next we use a For loop to print the data to the report. This example prints 38 records (1 page) and a section label every 10 records. A space is placed after each record for readability using the MoveYPos() function.

For x% = 1 To 38

    If x% = 1 Or x% = 11 Or x% = 21 Or x% = 31 Then

        MoveYPos (20)

To create the column headers, divide the record number by 10 and convert the result to an integer using the CInt() function. If you fail to use CInt(), rounding causes the section numbers to be 1.1, 2.2, and so on.

    i% = PrintColumnHeaders("Section " & Trim$(Str$(CInt(x% / 10) + 1)), 1, _ColumnWidth(1), "Arial", 12, BOLD_FONT)

End If

The next step is to piece together the data to display in the columns on the report. We re-use the variable szHeader for our data and it is passed using either the PrintColumnHeaders() or PrintColumnText() function. These two functions produce the same result, but PrintColumnHeaders() gives you more control over changing the column width and font. In this example, we concatenate the field title with the data to displayed:

'Place data from database or data file into ReportData array. In this case, we use _the sample

'ColumnData structure.

    ReportData$ = "Order Number: " & ColumnData(x%).OrderNum & Chr$(9)

    ReportData$ = ReportData$ + "Order Date: " & ColumnData(x%).OrderDate + Chr$(9)

    ReportData$ = ReportData$ + "Customer Number: " + ColumnData(x%).CustNum + _Chr$(9)

    ReportData$ = ReportData$ + "Serial Number: " + ColumnData(x%).SerNum + Chr$(9)

 'Print the report data.

    i% = PrintColumnHeaders(ReportData$, 4, ColumnWidth(1), "Arial", 10, BOLD_FONT)

 'This is a trick to put more space between each line of the report

    MoveYPos (5)

Next x%

    'These functions do general cleanup.

    i% = FinishParagraph()

    i% = EndColumnPrinting

    i% = DonePrinting()

A Sample Printing Program Using VBPRINT.DLL

This sample program creates a report and prints it to the Windows default printer using VBPRINT.DLL. The data for the report is created randomly and placed into a structure containing the field elements that are to be printed on the report. An underlined title is placed on the report and columns are created to output the data. Finally the report is sent to the printer. This sample assumes that the VBPRINT.DLL function declarations are present in the project.

Private Sub Command1_Click()

     'Return value for functions

     Dim i As Integer

    'Array that sets the widths of the columns on the report

    'We set the 4 column widths to 150 (1/100th of an inch, 100 = 1 inch)

    Dim ColumnWidth() As Integer

    ReDim ColumnWidth(1 To 4)

    For x% = 1 To 4

        ColumnWidth(x%) = 150

    Next x%

    'Title variable

    Dim szHeadLine As String

    szHeadLine$ = Trim$(Chr$(9) + Chr$(9) + Chr$(9) + Chr$(9) + "Customer Orders _for August, 1995")

    'szFontName$ = "Arial"

    'Structure that holds the reporting data

    Dim ColumnData(1 To 40) As OrderData

    'Fill the structure with bogus reporting data (this will normally come from _your database or data file).

    For x% = 1 To 40

        'Order Number field

        ColumnData(x%).OrderNum = ""

        ColumnData(x%).OrderNum = Trim$(Str$(x% & "000000"))

        'Order Date field

        ColumnData(x%).OrderDate = ""

        ColumnData(x%).OrderDate = Trim$(Str$(Date - x%))

        'Customer Number field

        ColumnData(x%).CustNum = ""

        ColumnData(x%).CustNum = Trim$(Str$(x% * 550))

        'Serial Number field

        ColumnData(x%).SerNum = ""

        ColumnData(x%).SerNum = Trim$(Str$(x% * 742))

    Next x%

    'Initialize the printer (pass it the handle of the main form)

    i% = InitializePrinter(Form1.hWnd)

    'Set the margins for the page layout (top, right, bottom, left)

    i% = PageLayoutSetup(10, 100, 100, 100)

    'Begin a paragraph. Set the font to Arial 12 point bold)

    i% = StartParagraph("Arial", 10, BOLD_FONT)

    'Print a title for the report (using the font and size set in StartParagraph

    i% = PrintHeadline(szHeadLine$, "Arial", 14, BOLD_FONT Or TOP_BORDER)

    'Draw a line under the report title

    i% = DrawLine(100, 1500, 12000, 1500)

    'Set up 4 columns in which to print the reporting data.

    i% = SetUpColumns(4, 100, "Arial", 12, BOLD_FONT)

    'Move down the page to place a space after the report title and before the _column headings

    MoveYPos (40)

    'Draw a line under the column titles

    i% = DrawLine(100, 2100, 12000, 2100)

    'Construct and print the titles for the columns

    szHeader$ = "Order Number" + Chr$(9) + "Order Date" + Chr$(9) + "Customer _Number " + Chr$(9) + "Serial Number"

    i% = PrintColumnHeaders(szHeader$, 4, ColumnWidth(1), "Arial", 12, BOLD_FONT)

    'Move down the page to place a space after the column headings and before the _report data.

    MoveYPos (10)

    'Place the reporting data into ReportData$. Concatenate the title of the column _with the data.

    'All of the columns for the page are assembled into the ReportData$ variable. _This prints the data

    'across the page when sent to the PrintColumnHeaders function.

    For x% = 1 To 38 '(38 records is one page, normally you would use _UBound(ReportData$)

        'Print a section label before each set of records (every 10 records in this _example)

        If x% = 1 Or x% = 11 Or x% = 21 Or x% = 31 Then

            'Place a space after the previous set of records and before the section _label

            MoveYPos (20)

            'Print a section number for the next section (CInt(x/10) is used to get _a heading number. Without CInt, you

            'get nice things like section 1.1 or 2.1. This is only for purposes of _this example.

            i% = PrintColumnHeaders("Section " & Trim$(Str$(CInt(x% / 10) + 1)), 1, _ColumnWidth(1), "Arial", 12, BOLD_FONT)

        End If

        'Place data from database or data file into ReportData array. In this case, _we use the sample

        'ColumnData structure.

        ReportData$ = "Order Number: " & ColumnData(x%).OrderNum & Chr$(9)

        ReportData$ = ReportData$ + "Order Date: " & ColumnData(x%).OrderDate + _Chr$(9)

        ReportData$ = ReportData$ + "Customer Number: " + ColumnData(x%).CustNum + _Chr$(9)

        ReportData$ = ReportData$ + "Serial Number: " + ColumnData(x%).SerNum + _Chr$(9)

        'Print the report data.

        i% = PrintColumnHeaders(ReportData$, 4, ColumnWidth(1), "Arial", 10, _BOLD_FONT)

        'This is a trick to put more space between each line of the report

        MoveYPos (5)

    Next x%

    'These functions do general cleanup.

    i% = FinishParagraph()

    i% = EndColumnPrinting

    i% = DonePrinting()

End Sub

The following is a sample of the report output:

Customer Orders for August, 1995

_________________________________________________________________________________________

                     Order Number   Order Date            Customer Number               Serial Number

Section 1

Order Number:        1000000        Order Date: 6/16/95   Customer Number: 550          Serial Number: 742

Order Number:        2000000        Order Date: 6/15/95   Customer Number: 1100         Serial Number: 1484

Order Number:        3000000        Order Date: 6/14/95   Customer Number: 1650         Serial Number: 2226

Order Number:        4000000        Order Date: 6/13/95   Customer Number: 2200         Serial Number: 2968

.

.

.

Section 2

Order Number:     1100000   Order Date: 6/6/95      Customer Number: 6050          Serial Number: 8162

Order Number:     1200000   Order Date: 6/5/95      Customer Number: 6600          Serial Number: 8904

Order Number:     1300000   Order Date: 6/4/95      Customer Number: 7150          Serial Number: 9646

Order Number:     1400000   Order Date: 6/3/95      Customer Number: 7700          Serial Number: 10388

VBPRINT.DLL Functions

The following is an index of the functions contained within VBPRINT.DLL. The declaration is supplied for each function along with an explanation of its use.

InitializePrinter()

Declaration: InitializePrinter(ByVal hWnd%) as integer

Description: This is the first function called when setting up a print job. Pass the hWnd of the topmost application window as hWnd%.

Usage: i% = InitializePrinter(Main.hWnd)

PageLayoutSetup()

Declaration: PageLayoutSetup(ByVal nTop%, ByVal nRight%, ByVal nBottom%, ByVal nLeft%) as integer

Description: Sets the top, right, left, and bottom margins for the page. All printing takes place within these margins. If there is more data than can fit on one page (horizontally or vertically), additional pages are created to print the data.

Usage: i% = PageLayoutSetup(10, 100, 100, 100)

DonePrinting()

Declaration: DonePrinting() as integer

Description: This is the final function called. It is called when the print job is completed. This function performs general cleanup after the print job.

Usage: DonePrinting()

StartParagraph()

Declaration: StartParagraph(ByVal szFontName$, ByVal nFontSize%, ByVal nStyle%) as integer

Description: This function is called to set up the font for a paragraph. Call this function before sending text to the paragraph. The parameters are as follows:

szFontName—The name of the font to use for the paragraph. This is either a variable or a quoted string. For example, szFontName = "Arial"

szFontSize—The size of the font in points. This is passed as an integer (ex: 8)

nStyle—Printing specifics for the font. These are defined as follows:

#define BOLD_FONT        0x0001

#define ITALIC_FONT      0x0002

#define TOP_BORDER       0x0004

#define LEFT_BORDER      0x0008

#define RIGHT_BORDER     0x0010

#define BOTTOM_BORDER    0x0020

#define CHECK_BOX        0x0100

Usage: i% = StartParagraph("Arial", 10, BOLD_FONT)

PrintParagraphText()

Declaration: PrintParagraphText(ByVal szText$) as integer

Description: Call this function to add text to a paragraph. Call this function as many times as desired to fill the paragraph.

Usage: An example where multiple calls can be useful:

Dim ReportData$(1 to 20) as string

'Fill ReportData from a data file

...

'Place the text from ReportData in the paragraph

For x% = 1 to UBound(ReportData$)

    szText$ = ReportData(x%)

    PrintParagraphText(szText)

Next x%

This loop sequentially adds each element of the ReportData array to the current paragraph. Use FinishParagraph() and StartParagraph() to create additional paragraphs. For example:

'Place the text from ReportData in the paragraph

For x% = 1 to UBound(ReportData$)

        If x%%5 = 0 Then 'Do this every fifth time through the loop (when x/5 = 0)

        I% = FinishParagraph()

        i% = StartParagraph("Arial", 10, BOLD_FONT)

    Endif

    szText$ = ReportData(x%)

    PrintParagraphText(szText)

Next x%

FinishParagraph()

Declaration: FinishParagraph() as integer

Description: Called at the end of printing a paragraph to do general cleanup.

Usage: FinishParagraph()

PrintHeadline()

Note

The readme.txt file associated with this DLL has this function incorrectly listed as HeadlinePrint().

Declaration: PrintHeadline(ByVal szHeadLine$, ByVal szFontName$, ByVal nFontSize%, ByVal nStyle%) as integer

Description: Prints a title for the report. This function has the following parameters:

szHeadLine$—Text to be printed in the title

szFontName$—Font name to be used in the title

nFontSize%—Font size as an integer (must be a valid size for the selected font)

nStyle—Style flags for the font. (See the StartParagraph function for a description of these flags.)

Usage: i% = PrintHeadline(szHeadLine$, "Arial", 14, BOLD_FONT Or TOP_BORDER)

SetParagraphSpacing()

Declaration: SetParagraphSpacing(ByVal nSpacing%) as integer

Description: This function sets the number of spaces to place between the end of one paragraph and the beginning of the next. Spacing is set in points. The default is 18 points or 1/4 inch.

Usage: I% = SetParagraphSpacing(18)

EjectPage()

Declaration: EjectPage() as integer

Description: Ejects the current page from the printer. Any text that has already been sent for printing is printed. A new page is set up for printing and the cursor is set at the top left corner of the new page.

Usage: I% = EjectPage()

PrintDLLVersion()

Declaration: PrintDLLVersion() as integer

Description: Returns the version of VBPRINT.DLL; the major version number is in the low byte number of the integer, and the minor version number is in the high byte number of the integer.

Usage: I% = PrintDLLVersion

SetUpColumns()

Declaration: SetUpColumns(ByVal Columns%, ByVal nC1%, ByVal szFontName$, ByVal nFontSize%, ByVal nStyle%) as integer

Description: This function sets columns for the report. It contains the following parameters:

Columns%—The number of columns to be printed on the report.

nC1%—An integer array, each element containing the width of a specific column. This parameter is passed to several functions to set the width of the column being printed. The settings are in hundredths of an inch. 100 = 1". The maximum number of columns allowed is 8.

szFontName$—A string containing the name of the font to be used in the column when printed.

szFontSize%—The size of the font used in the column during printing.

nStyle%—Style flags for the font. (See the StartParagraph function for a description of these flags.)

Usage: i% = SetUpColumns(4, 100, "Arial", 12, BOLD_FONT)

PrintColumnText()

Declaration: PrintColumnText(ByVal szText$) as integer

Description: Use this function to send tab delimited text into a paragraph. Used to send text into the columns.

Usage: I% = PrintColumnText("Have" & Chr$(9) & "a" & Chr$(9) & "nice" & Chr$(9) & "day") prints

Have          a          nice         day

EndColumnPrinting()

Declaration: EndColumnPrinting() as integer

Description: Used at the end of printing columns to do general cleanup.

Usage: I% = EndColumnPrinting()

PrintColumnHeaders()

Declaration: PrintColumnHeaders(ByVal szHeader$, ByVal Columns%, ByVal nC1%, ByVal szFontName$, ByVal nFontSize%, ByVal nStyle%) as integer

Description: Prints the titles of the columns on the report. This function contains the following parameters:

szHeader$—The data that is printed to the column.

szFontName$—A string containing the name of the font to be used in the column when printed.

szFontSize%—The size of the font used in the column during printing.

nStyle%—Style flags for the font. (See the StartParagraph function for a description of these flags.)

Usage: i% = PrintColumnHeaders(ReportData$, 4, ColumnWidth(1), "Arial", 10, BOLD_FONT)

MoveYPos()

Declaration: MoveYPos( ByVal nY%) as integer

Description: Moves the current position on the page for printing downward vertically by the value of Y%. Y% is in intervals of hundredths of an inch.

Usage: I% = MoveYPos(30)

DrawLine()

Declaration: DrawLine( ByVal nX1%, ByVal nY1%, ByVal nX2%, ByVal nY2%) as integer

Description: Draws a line from position (nX1, nY1) to (nX2, nY2). The coordinate system used for plotting is TWIPS. The parameters used in this function are:

nX1—Top left vertical position

nY1—Top left horizontal position

nX2—Bottom right vertical position

nY2—Bottom right horizontal position

Usage: i% = DrawLine(100, 2100, 12000, 2100)

DrawRectangle()

Declaration: DrawRectangle( ByVal nX1%, ByVal nY1%, ByVal nX2%, ByVal nY2%) as integer

Description: Draws a Rectangle using the following coordinates in TWIPS:

nX1—Top left vertical position

nY1—Top left horizontal position

nX2—Bottom right vertical position

nY2—Bottom right horizontal position

Usage: I% = DrawRectangle(100, 100, 5000, 5000)

DrawRndRectangle()

Declaration: DrawRndRectangle( ByVal nX1%, ByVal nY1%, ByVal nX2%, ByVal nY2%, ByVal nX3%, ByVal nY3%) as integer

Description: Draws a Rectangle with rounded corners. The following parameters are used in this function:

nX1—Top left vertical position

nY1—Top left horizontal position

nX2—Bottom right vertical position

nY2—Bottom right horizontal position

nX3—Height of the rounded corners

nY3—Width of the rounded corners

Usage: I% = DrawRndRectangle(100, 100, 5000, 5000, 500, 500)

DrawEllipse()

Declaration: DrawEllipse(ByVal nX1%, ByVal nY1%, ByVal nX2%, ByVal nY2%) as integer

Description: Draws an ellipse on the report. The parameters (in TWIPS) used in this function are:

nX1—Top left vertical position

nY1—Top left horizontal position

nX2—Bottom right vertical position

nY2—Bottom right horizontal position

Usage: I% = DrawEllipse(100, 100, 5000, 5000)

Summary

Although there are no reporting mechanisms built directly into Visual Basic, this chapter has demonstrated that there are many avenues to pursue when developing reporting for Visual Basic applications. Crystal Reports, the Crystal Reports OCX, and VBPRINT.DLL provide significant functionality and flexibility when creating quality reporting for Visual Basic applications.